package com.zhufei.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.Sponsor;
import com.zhufei.entity.ZFAdministrator;

public class ZFAdministratorDao extends DBCon{
	private static String TABLE = "zhufei_administrator";
	private static String ID = "ID";
	private static String NAME = "administrator_name";
	private static String PASSWORD = "password";
	private static String REGION = "region";
	private static String CLASS = "class";
	/**
	 * 获取数据库中管理员数量
	 * @return  返回数据库有多少个管理员
	 */
	public static int getCountOfAdministrator(){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE;
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				rs.close();
				return count;
			}
			else {
				rs.close();
				return 0;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 插入一个管理员到数据库
	 * @param name 用户名
	 * @param password 密码
	 * @param region 所属区域
	 * @param class_ 管理员类别
	 * @return true表示插入成功，false表示失败
	 */
	public static boolean insertAdministrator(String name, String password, String region, int class_){
		DBCon con = new DBCon();
		try {
			String id = GlobalsDao.getUUID();
			String dt = GlobalsDao.getCurrentDateTime();
			Statement statement = con.getStatement();
			String sql = "insert into " + TABLE + " ("+ID+","+NAME+","+PASSWORD+","+REGION+","+CLASS+") values ('"+id+"','"+name+"','"+password+"','"+region+"',"+class_+")";
			statement.execute(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 判断该用户名是否存在
	 * @param name 用户名
	 * @return
	 */
	public static boolean isNameExist(String name){
		DBCon con = new DBCon();
		try{
			Statement statement = con.getStatement();
			String sql = "select * from " + TABLE + " where " + NAME +"='"+name+"'";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				rs.close();
				return true;	
			}else{
				rs.close();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		
		return false;
	}
	
	/**
	 * 删除管理员
	 * @param id     id
	 * @return  true表示成功，false表示失败
	 */
	public static boolean deleteAdministrator(String id){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "delete from "+TABLE+" where ID ='"+id+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 修改管理员信息
	 * @param id  管理员id
	 * @param password 密码
	 * @param region 区域
	 * @param class_ 类别
	 * @return 修改成功返回true，否则返回false
	 */
	public static boolean updateAdministrator(String id, String password, String region, int class_){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set "+PASSWORD+"='"+password+"',"+REGION+"='"+region+"',"+CLASS+"="+class_+" where "+ID+"='"+id+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的管理员对象
	 * @param index 从第index条开始获取（第一条下标是0）
	 * @param number 获取管理员的数量，输入-1则获取数据库中所有管理员
	 * @return  管理员对象的ArrayList
	 */
	public static ArrayList<ZFAdministrator> getAdministrators(int index, int number){
		ArrayList<ZFAdministrator> administratorList = new ArrayList<ZFAdministrator>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			if(number != -1){
				sql = "select * from "+ TABLE +" limit "+index+","+number;
			}else{
				sql = "select * from "+ TABLE;
			}
			System.out.println(sql);
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				ZFAdministrator administrator = new ZFAdministrator();
				administrator.setID(rs.getString(ID));
				administrator.setAdministrator_name(rs.getString(NAME));
				administrator.setPassword(rs.getString(PASSWORD));
				administrator.setRegion(rs.getString(REGION));
				administrator.setClass_(rs.getInt(CLASS));
				administratorList.add(administrator);
			}
			rs.close();
			return administratorList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 根据用户名获取管理员对象
	 * @param name  用户名
	 * @return
	 */
	public static ZFAdministrator getAdministrator(String name){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			sql = "select * from "+ TABLE +" where "+NAME+"='"+name+"'";
			System.out.println(sql);
			ResultSet rs = statement.executeQuery(sql);
			ZFAdministrator administrator = new ZFAdministrator();
			if(rs.next()){
				administrator.setID(rs.getString(ID));
				administrator.setAdministrator_name(rs.getString(NAME));
				administrator.setPassword(rs.getString(PASSWORD));
				administrator.setRegion(rs.getString(REGION));
				administrator.setClass_(rs.getInt(CLASS));
			}
			rs.close();
			return administrator;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
}
